﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class PhongDAO:AbstractDAO
    {
        public static List<PhongDTO> layDSPhong()
        {
            OleDbConnection ketNoi = null;
            List<PhongDTO> dsPhong = new List<PhongDTO>();
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "SELECT MaPhong,TenPhong,P.MaLoaiPhong,TenLoaiPhong,GhiChu,DonGia,SoKhachHienCo FROM Phong P,LoaiPhong LP WHERE P.MaLoaiPhong=LP.MaLoaiPhong ";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    PhongDTO phong = new PhongDTO();
                    phong.MaPhong = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        phong.TenPhong = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(4))
                        phong.GhiChu = boDoc.GetString(4);
                    if (!boDoc.IsDBNull(6))
                        phong.SoKhachHienCo = boDoc.GetInt32(6);


                    LoaiPhongDTO loaiPhong = new LoaiPhongDTO();
                    loaiPhong.MaLoaiPhong = boDoc.GetInt32(2);
                    loaiPhong.TenLoaiPhong = boDoc.GetString(3);
                    loaiPhong.DonGia = boDoc.GetInt32(5);
                    phong.LoaiPhong = loaiPhong;

                    dsPhong.Add(phong);
                }

            }
            catch
            {
                dsPhong = new List<PhongDTO>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();  
            }
            return dsPhong;
        }
        public static List<PhongDTO> layDSPhongTheoMaLoaiPhong(long maLoaiPhong)
        {
            OleDbConnection ketNoi = null;
            List<PhongDTO> dsPhong = new List<PhongDTO>();
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "SELECT MaPhong,TenPhong,P.MaPhong,TenLoaiPhong,GhiChu,DonGia,SoKhachHienCo FROM Phong P,LoaiPhong LP WHERE P.MaLoaiPhong=LP.MaLoaiPhong AND P.MaLoaiPhong=@MaLoaiPhong";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbParameter thamSo = new OleDbParameter("@MaLoaiPhong", OleDbType.Integer);
                thamSo.Value = maLoaiPhong;

                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();

                while (boDoc.Read())
                {
                    PhongDTO phong = new PhongDTO();
                    phong.MaPhong = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        phong.TenPhong = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(4))
                        phong.GhiChu = boDoc.GetString(4);
                    if (!boDoc.IsDBNull(6))
                        phong.SoKhachHienCo = boDoc.GetInt32(6);
                    
                    LoaiPhongDTO loaiPhong = new LoaiPhongDTO();
                    loaiPhong.MaLoaiPhong = boDoc.GetInt32(2);
                    loaiPhong.TenLoaiPhong = boDoc.GetString(3);
                    loaiPhong.DonGia = boDoc.GetInt32(5);
                    phong.LoaiPhong = loaiPhong;

                    dsPhong.Add(phong);
                }
            }
            catch(Exception ex)
            {
                dsPhong = new List<PhongDTO>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();   
            }
            return dsPhong;
        }
        public static bool capNhat(PhongDTO phong)
        {
            bool ketQua = true;
            OleDbConnection ketNoi=null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "UPDATE Phong SET TenPhong=@TenPhong,GhiChu=@GhiChu,MaLoaiPhong=@MaLoaiPhong WHERE MaPhong=@MaPhong";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);
                OleDbParameter thamSo;

                thamSo = new OleDbParameter("@TenPhong", OleDbType.VarChar);
                thamSo.Value = phong.TenPhong;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@GhiChu", OleDbType.VarChar);
                thamSo.Value = phong.GhiChu;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaLoaiPhong", OleDbType.Integer);
                thamSo.Value = phong.LoaiPhong.MaLoaiPhong;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaPhong", OleDbType.Integer);
                thamSo.Value = phong.MaPhong;
                lenh.Parameters.Add(thamSo);


                lenh.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;

        }
        public static bool xoa(long maPhong)
        {
            bool ketQua = true;
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "DELETE FROM Phong WHERE MaPhong=@MaPhong";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@MaPhong", OleDbType.Integer);
                thamSo.Value = maPhong;
                lenh.Parameters.Add(thamSo);

                lenh.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }
        public static bool them(PhongDTO phong,long maLoaiPhong)
        {
            bool ketQua = true;
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "INSERT INTO Phong(TenPhong,GhiChu,MaLoaiPhong)"; 
                chuoiLenh += " VALUES (@TenPhong,@GhiChu,@MaLoaiPhong)";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh,ketNoi);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@TenPhong",OleDbType.VarChar);
                thamSo.Value = phong.TenPhong;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@GhiChu",OleDbType.VarChar);
                thamSo.Value = phong.GhiChu;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaLoaiPhong",OleDbType.Integer);
                thamSo.Value = maLoaiPhong;
                lenh.Parameters.Add(thamSo);

                lenh.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }
        public static bool capNhatSoKhachHienCo(long maPhong,long soKhach)
        {
            bool ketQua = true;
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "UPDATE Phong SET SoKhachHienCo=@SoKhachHienCo WHERE MaPhong=@MaPhong";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);
                OleDbParameter thamSo;

                thamSo = new OleDbParameter("@SoKhachHienCo", OleDbType.Integer);
                thamSo.Value = soKhach;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaPhong", OleDbType.Integer);
                thamSo.Value = maPhong;
                lenh.Parameters.Add(thamSo);

                lenh.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;

        }

    }
}
